{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-09-27T07:46:22.702169900Z",
     "start_time": "2023-09-27T07:46:02.839094600Z"
    }
   },
   "outputs": [],
   "source": [
    "#2024年11月快报导入\n",
    "import os\n",
    "import xlwings as xw\n",
    "import openpyxl\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "file_name='2024年11月快报.xlsx'\n",
    "file_gx='2024年度综合计划建议附表.xlsx'\n",
    "app=xw.App(visible=False,add_book=False)\n",
    "workbook1=app.books.open(file_name)\n",
    "worksheet1=workbook1.sheets[0]\n",
    "workbook2=app.books.open(file_gx)\n",
    "worksheet2=workbook2.sheets('生产经营指标')\n",
    "try:\n",
    "    fdl=worksheet1.range('D442').value ##发电量\n",
    "    fcsdl=worksheet1.range('D463').value ##非生产用电量\n",
    "    sdl=worksheet1.range('D469').value  ##售电量\n",
    "    fdcydl=worksheet1.range('D482').value/100 ##发电厂用电率\n",
    "    grcydl=worksheet1.range('D492').value ##供热厂用电率\n",
    "    grl=worksheet1.range('D477').value/10000  #供热量\n",
    "    srl=worksheet1.range('D479').value/10000  #售热量\n",
    "    gdmh=worksheet1.range('D548').value ##供电煤耗\n",
    "    grmh=worksheet1.range('D555').value  ##供热煤耗\n",
    "    fdbmj=worksheet1.range('D549').value ##发电综合标煤价\n",
    "    grbmj=worksheet1.range('D556').value ##供热综合标煤价\n",
    "    sddj=worksheet1.range('D513').value ##售电单价\n",
    "    srdj=worksheet1.range('D538').value ##售热单价\n",
    "    qtywsr=worksheet1.range('D117').value ##其它业务收入\n",
    "    sf=worksheet1.range('D332').value+worksheet1.range('D379').value  ##水费\n",
    "    wgdf=worksheet1.range('D333').value ##网购电费\n",
    "    clf=worksheet1.range('D335').value+worksheet1.range('D380').value ##材料费\n",
    "    zgxc=worksheet1.range('D338').value+worksheet1.range('D381').value ##职工薪酬\n",
    "    gz=worksheet1.range('D339').value+worksheet1.range('D382').value ##工资\n",
    "    zjf=worksheet1.range('D341').value+worksheet1.range('D384').value  ##折旧费\n",
    "    xlf=worksheet1.range('D342').value+worksheet1.range('D385').value  ##修理费\n",
    "    wtyx=worksheet1.range('D343').value+worksheet1.range('D386').value  ##委托运行费\n",
    "    qtfy=worksheet1.range('D344').value+worksheet1.range('D387').value  ##其他费用\n",
    "    qtywcb=worksheet1.range('D140').value  #其他业务成本\n",
    "    yysj=worksheet1.range('D151').value  ##营业税金\n",
    "    cwfy=worksheet1.range('D195').value  ##财务费用\n",
    "    qtsy=worksheet1.range('D210').value  ##其他收益\n",
    "    yywsr=worksheet1.range('D287').value  ##营业外收入\n",
    "    yywzc=worksheet1.range('D299').value  ##营业外支出\n",
    "    yffy=worksheet1.range('D184').value ##研发费用\n",
    "    glfy=worksheet1.range('D173').value ##管理费用\n",
    "    ##将上述变量存入综合计划建议表中\n",
    "    worksheet2.range('AS3').value=fdl ##发电量\n",
    "    worksheet2.range('AS4').value=fdl*fdcydl  ##发电厂用电量\n",
    "    worksheet2.range('AS5').value=fdl-fdl*fdcydl  ##供电量\n",
    "    worksheet2.range('AS6').value=grcydl*grl  ##供热厂用电量\n",
    "    worksheet2.range('AS7').value=fdl-fdl*fdcydl-grcydl*grl  ##厂供电量\n",
    "    #worksheet2.range('AS8').value=fdl-fdl*fdcydl-fdl*(1-cfdzhcydl) ##变损电量\n",
    "    worksheet2.range('AS9').value=fcsdl  ##非生产用电量\n",
    "    worksheet2.range('AS10').value=sdl ##售电量\n",
    "    worksheet2.range('AS11').value=fdcydl ##发电厂用电率\n",
    "    worksheet2.range('AS12').value=grcydl ##供热厂用电率\n",
    "\n",
    "    worksheet2.range('AS15').value=grl ##供热量\n",
    "    worksheet2.range('AS16').value=srl ##售热量\n",
    "    worksheet2.range('AS17').value=gdmh ##供电煤耗\n",
    "    fdzhbml=(gdmh*(fdl-fdl*fdcydl))/1000000 ##发电综合标煤量\n",
    "    worksheet2.range('AS18').value=fdzhbml ##发电综合标煤量\n",
    "    worksheet2.range('AS19').value=grmh  ##供热煤耗\n",
    "    worksheet2.range('AS20').value=grmh*grl/1000  ##供热综合标煤量\n",
    "    worksheet2.range('AS22').value=fdbmj  ##发电标煤单价\n",
    "    worksheet2.range('AS23').value=grbmj #供热标煤单价\n",
    "    fdywrlcb=fdbmj*fdzhbml #发电业务燃料费\n",
    "    grywrlcb=grbmj*grmh*grl/1000 #供热业务燃料费\n",
    "    \n",
    "    dlywsr=sddj*sdl/1000 #电力业务收入\n",
    "    grywsr=srl*srdj #供热业务收入\n",
    "    zyywsr=dlywsr+grywsr #主营业务收入\n",
    "    worksheet2.range('AS25').value=sddj ##售电单价\n",
    "    worksheet2.range('AS26').value=srdj ##售热单价\n",
    "    worksheet2.range('AS27').value=zyywsr+qtywsr #营业收入\n",
    "    worksheet2.range('AS28').value=zyywsr #主营业务收入\n",
    "    worksheet2.range('AS29').value=dlywsr #电力业务收入\n",
    "    worksheet2.range('AS30').value=grywsr #供热业务收入\n",
    "    worksheet2.range('AS31').value=qtywsr ##其它业务收入\n",
    "    crlwcb=sf+wgdf+clf+zgxc+zjf+xlf+wtyx+qtfy #除燃料外其它成本\n",
    "    rlf=fdywrlcb+grywrlcb #燃料费\n",
    "    worksheet2.range('AS24').value=rlf/(fdzhbml+grmh*grl/1000) #发电供热综合标煤单价\n",
    "    zyywcb=rlf+crlwcb #主营业务成本\n",
    "    yycb=zyywcb+qtywcb #营业成本\n",
    "    worksheet2.range('AS32').value=yycb ##营业成本\n",
    "    worksheet2.range('AS33').value=zyywcb ##主营业务成本\n",
    "    worksheet2.range('AS34').value=rlf #燃料费\n",
    "    worksheet2.range('AS35').value=fdywrlcb #发电业务燃料成本\n",
    "    worksheet2.range('AS36').value=grywrlcb #供热业务燃料成本\n",
    "    worksheet2.range('AS37').value=crlwcb #除燃料外其它成本\n",
    "    worksheet2.range('AS38').value=sf  ##水费\n",
    "    worksheet2.range('AS39').value=wgdf   ##网购电费\n",
    "    worksheet2.range('AS40').value=clf  ##材料费\n",
    "    worksheet2.range('AS41').value=zgxc ##职工薪酬\n",
    "    worksheet2.range('AS42').value=gz  ##工资\n",
    "    worksheet2.range('AS43').value=zjf  ##折旧费\n",
    "    worksheet2.range('AS44').value=xlf ##修理费\n",
    "    worksheet2.range('AS45').value=wtyx ##委托运行费\n",
    "    worksheet2.range('AS46').value=qtfy  ##其它费用\n",
    "    worksheet2.range('AS47').value=qtywcb  ##其它业务成本\n",
    "    worksheet2.range('AS48').value=yysj  ##营业税金\n",
    "    worksheet2.range('AS49').value=cwfy  ##财务费用\n",
    "    worksheet2.range('AS52').value=qtsy  ##其它收益\n",
    "    worksheet2.range('AS53').value=yywsr  ##营业外收入\n",
    "    worksheet2.range('AS54').value=yywzc  ##营业外支出\n",
    "    worksheet2.range('AS51').value=glfy  ##管理费用\n",
    "    worksheet2.range('AS55').value=yffy  ##研发费用\n",
    "\n",
    "    worksheet2.range('AS56').value=zyywsr+qtywsr-yycb-yysj-cwfy+qtsy+yywsr-yywzc-yffy-glfy       ##利润总额\n",
    "    worksheet2.range('AS3,AS9:AS12,AS15:AS17,AS19,AS22:AS23,AS25:AS26,AS31,AS38:AS49,AS51:AS55').color=(242,220,219)\n",
    "    worksheet2.range('AS4:AS8,AS13,AS18,AS20,AS24,AS27:AS30,AS32:AS37,AS56').color=(197,217,241)\n",
    "    area=worksheet2.range('AS3:AS56')\n",
    "    area.font.Size=10\n",
    "    area.font.name='仿宋'\n",
    "\n",
    "finally:\n",
    "    workbook1.close()\n",
    "    workbook2.save()\n",
    "    workbook2.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-09-26T08:29:20.582682800Z",
     "start_time": "2023-09-26T08:29:08.941096600Z"
    }
   },
   "outputs": [],
   "source": [
    "#2024年累计快报导入\n",
    "import os\n",
    "import xlwings as xw\n",
    "import openpyxl\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "file_name='2024年11月快报.xlsx'\n",
    "file_gx='2024年度综合计划建议附表.xlsx'\n",
    "app=xw.App(visible=False,add_book=False)\n",
    "workbook1=app.books.open(file_name)\n",
    "worksheet1=workbook1.sheets[0]\n",
    "workbook2=app.books.open(file_gx)\n",
    "worksheet2=workbook2.sheets('生产经营指标')\n",
    "try:\n",
    "    fdl=worksheet1.range('E442').value ##发电量\n",
    "    fcsdl=worksheet1.range('E463').value ##非生产用电量\n",
    "    sdl=worksheet1.range('E469').value  ##售电量\n",
    "    fdcydl=worksheet1.range('E482').value/100 ##发电厂用电率\n",
    "    grcydl=worksheet1.range('E492').value ##供热厂用电率\n",
    "    grl=worksheet1.range('E477').value/10000  #供热量\n",
    "    srl=worksheet1.range('E479').value/10000  #售热量\n",
    "    gdmh=worksheet1.range('E548').value ##供电煤耗\n",
    "    grmh=worksheet1.range('E555').value  ##供热煤耗\n",
    "    fdbmj=worksheet1.range('E549').value ##发电综合标煤价\n",
    "    grbmj=worksheet1.range('E556').value ##供热综合标煤价\n",
    "    sddj=worksheet1.range('E513').value ##售电单价\n",
    "    srdj=worksheet1.range('E538').value ##售热单价\n",
    "    qtywsr=worksheet1.range('E117').value ##其它业务收入\n",
    "    sf=worksheet1.range('E332').value+worksheet1.range('E379').value  ##水费\n",
    "    wgdf=worksheet1.range('E333').value ##网购电费\n",
    "    clf=worksheet1.range('E335').value+worksheet1.range('E380').value ##材料费\n",
    "    zgxc=worksheet1.range('E338').value+worksheet1.range('E381').value ##职工薪酬\n",
    "    gz=worksheet1.range('E339').value+worksheet1.range('E382').value ##工资\n",
    "    zjf=worksheet1.range('E341').value+worksheet1.range('E384').value  ##折旧费\n",
    "    xlf=worksheet1.range('E342').value+worksheet1.range('E385').value  ##修理费\n",
    "    wtyx=worksheet1.range('E343').value+worksheet1.range('E386').value  ##委托运行费\n",
    "    qtfy=worksheet1.range('E344').value+worksheet1.range('E387').value  ##其他费用\n",
    "    qtywcb=worksheet1.range('E140').value  #其他业务成本\n",
    "    yysj=worksheet1.range('E151').value  ##营业税金\n",
    "    cwfy=worksheet1.range('E195').value  ##财务费用\n",
    "    qtsy=worksheet1.range('E210').value  ##其他收益\n",
    "    yywsr=worksheet1.range('E287').value  ##营业外收入\n",
    "    yywzc=worksheet1.range('E299').value  ##营业外支出\n",
    "    yffy=worksheet1.range('E184').value ##研发费用\n",
    "    glfy=worksheet1.range('E173').value ##管理费用\n",
    "   \n",
    "    ##将上述变量存入综合计划建议表中\n",
    "    worksheet2.range('J3').value=fdl ##发电量\n",
    "    worksheet2.range('J4').value=fdl*fdcydl  ##发电厂用电量\n",
    "    worksheet2.range('J5').value=fdl-fdl*fdcydl  ##供电量\n",
    "    worksheet2.range('J6').value=grcydl*grl  ##供热厂用电量\n",
    "    worksheet2.range('J7').value=fdl-fdl*fdcydl-grcydl*grl  ##厂供电量\n",
    "    #worksheet2.range('AS8').value=fdl-fdl*fdcydl-fdl*(1-cfdzhcydl) ##变损电量\n",
    "    worksheet2.range('J9').value=fcsdl  ##非生产用电量\n",
    "    worksheet2.range('J10').value=sdl ##售电量\n",
    "    worksheet2.range('J11').value=fdcydl ##发电厂用电率\n",
    "    worksheet2.range('J12').value=grcydl ##供热厂用电率\n",
    "\n",
    "    worksheet2.range('J15').value=grl ##供热量\n",
    "    worksheet2.range('J16').value=srl ##售热量\n",
    "    worksheet2.range('J17').value=gdmh ##供电煤耗\n",
    "    fdzhbml=(gdmh*(fdl-fdl*fdcydl))/1000000 ##发电综合标煤量\n",
    "    worksheet2.range('J18').value=fdzhbml ##发电综合标煤量\n",
    "    worksheet2.range('J19').value=grmh  ##供热煤耗\n",
    "    worksheet2.range('J20').value=grmh*grl/1000  ##供热综合标煤量\n",
    "    worksheet2.range('J22').value=fdbmj  ##发电标煤单价\n",
    "    worksheet2.range('J23').value=grbmj #供热标煤单价\n",
    "    fdywrlcb=fdbmj*fdzhbml #发电业务燃料费\n",
    "    grywrlcb=grbmj*grmh*grl/1000 #供热业务燃料费\n",
    "    \n",
    "    dlywsr=sddj*sdl/1000 #电力业务收入\n",
    "    grywsr=srl*srdj #供热业务收入\n",
    "    zyywsr=dlywsr+grywsr #主营业务收入\n",
    "    worksheet2.range('J25').value=sddj ##售电单价\n",
    "    worksheet2.range('J26').value=srdj ##售热单价\n",
    "    worksheet2.range('J27').value=zyywsr+qtywsr #营业收入\n",
    "    worksheet2.range('J28').value=zyywsr #主营业务收入\n",
    "    worksheet2.range('J29').value=dlywsr #电力业务收入\n",
    "    worksheet2.range('J30').value=grywsr #供热业务收入\n",
    "    worksheet2.range('J31').value=qtywsr ##其它业务收入\n",
    "    crlwcb=sf+wgdf+clf+zgxc+zjf+xlf+wtyx+qtfy #除燃料外其它成本\n",
    "    rlf=fdywrlcb+grywrlcb #燃料费\n",
    "    worksheet2.range('J24').value=rlf/(fdzhbml+grmh*grl/1000) #发电供热综合标煤单价\n",
    "    zyywcb=rlf+crlwcb #主营业务成本\n",
    "    yycb=zyywcb+qtywcb #营业成本\n",
    "    worksheet2.range('J32').value=yycb ##营业成本\n",
    "    worksheet2.range('J33').value=zyywcb ##主营业务成本\n",
    "    worksheet2.range('J34').value=rlf #燃料费\n",
    "    worksheet2.range('J35').value=fdywrlcb #发电业务燃料成本\n",
    "    worksheet2.range('J36').value=grywrlcb #供热业务燃料成本\n",
    "    worksheet2.range('J37').value=crlwcb #除燃料外其它成本\n",
    "    worksheet2.range('J38').value=sf  ##水费\n",
    "    worksheet2.range('J39').value=wgdf   ##网购电费\n",
    "    worksheet2.range('J40').value=clf  ##材料费\n",
    "    worksheet2.range('J41').value=zgxc ##职工薪酬\n",
    "    worksheet2.range('J42').value=gz  ##工资\n",
    "    worksheet2.range('J43').value=zjf  ##折旧费\n",
    "    worksheet2.range('J44').value=xlf ##修理费\n",
    "    worksheet2.range('J45').value=wtyx ##委托运行费\n",
    "    worksheet2.range('J46').value=qtfy  ##其它费用\n",
    "    worksheet2.range('J47').value=qtywcb  ##其它业务成本\n",
    "    worksheet2.range('J48').value=yysj  ##营业税金\n",
    "    worksheet2.range('J49').value=cwfy  ##财务费用\n",
    "    worksheet2.range('J52').value=qtsy  ##其它收益\n",
    "    worksheet2.range('J53').value=yywsr  ##营业外收入\n",
    "    worksheet2.range('J54').value=yywzc  ##营业外支出\n",
    "    worksheet2.range('J51').value=glfy   ##管理费用\n",
    "    worksheet2.range('J55').value=yffy   ##研发费用\n",
    "\n",
    "    worksheet2.range('J56').value=zyywsr+qtywsr-yycb-yysj-cwfy+qtsy+yywsr-yywzc-glfy-yffy       ##利润总额\n",
    "    \n",
    "    \n",
    "finally:\n",
    "    workbook1.close()\n",
    "    workbook2.save()\n",
    "    workbook2.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "228ec1c77332413f421e77c28617d1df6074d6a46921cdaeb15ed9feeb460bfe"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
